package cn.zy.company.dao;



import cn.zy.company.entity.News;
import cn.zy.company.util.DBUtil;
import cn.zy.company.util.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDao {
    public void add(News news) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.insert(connection, news);
        DataSource.closeConnection(connection);
    }

    public void delete(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.delete(connection, id, News.class);
        DataSource.closeConnection(connection);
    }

    public void update(News news) throws Exception {
        Connection connection = DataSource.getConnection();
        DBUtil.update(connection, news);
        DataSource.closeConnection(connection);
    }

    public News findById(Integer id) throws Exception {
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select n.*,u.username from news n left join user u on  n.id=u.id where n.id=?");
        preparedStatement.setObject(1, id);
        News news = null;
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getNString("value"), resultSet.getInt("id"), resultSet.getString("username"));
            news.setId(resultSet.getInt("id"));
            news.setDate(resultSet.getDate("date"));

        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return news;
    }

    public List<News> findAll(Integer page, Integer size) throws Exception {
        List<News> list = new ArrayList<News>();
        News news = null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select n.*,u.username  from news n left join user u on  n.id=u.id limit ?,?");
        preparedStatement.setObject(1, (page - 1) * size);
        preparedStatement.setObject(2, size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getNString("value"), resultSet.getInt("id"), resultSet.getString("username"));
            news.setId(resultSet.getInt("id"));
            news.setDate(resultSet.getDate("date"));
            list.add(news);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    public long countAll() throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "", new Object[]{}, News.class);
        DataSource.closeConnection(connection);
        return count;
    }

    public List<News> find(String name, Integer page, Integer size) throws SQLException {
        List<News> list=new ArrayList<News>();
        News news=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select n.*,u.username from news n left join user u on  n.id=u.id where n.name like ? limit ?,?");
        preparedStatement.setObject(1,"%" + name + "%");
        preparedStatement.setObject(2,(page-1)*size);
        preparedStatement.setObject(3,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getNString("value"), resultSet.getInt("id"), resultSet.getString("username"));
            news.setId(resultSet.getInt("id"));
            news.setDate(resultSet.getDate("date"));
            list.add(news);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    public List<News> findByUserId(String userId, Integer page, Integer size) throws SQLException {
        List<News> list=new ArrayList<News>();
        News news=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select n.*,u.username from news n left join user u on  n.id=u.id where n.id=? limit ?,?");
        preparedStatement.setObject(1, userId);
        preparedStatement.setObject(2,(page-1)*size);
        preparedStatement.setObject(3,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getNString("value"), resultSet.getInt("id"), resultSet.getString("username"));
            news.setId(resultSet.getInt("id"));
            news.setDate(resultSet.getDate("date"));
            list.add(news);
        }
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    public List<News> findByDate(String date, Integer page, Integer size) throws SQLException {
        List<News> list=new ArrayList<News>();
        News news=null;
        Connection connection = DataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select n.*,u.username from news n left join user u on  n.id=u.id where n.date=? limit ?,?");
        preparedStatement.setObject(1, date );
        preparedStatement.setObject(2,(page-1)*size);
        preparedStatement.setObject(3,size);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            news = new News(resultSet.getString("name"), resultSet.getNString("value"), resultSet.getInt("id"), resultSet.getString("username"));
            news.setId(resultSet.getInt("id"));
            news.setDate(resultSet.getDate("date"));
            list.add(news);
        }
        System.out.println(list);
        resultSet.close();
        preparedStatement.close();
        DataSource.closeConnection(connection);
        return list;
    }

    public long countByName(String name) throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "where name like ?", new Object[]{"%" + name + "%"}, News.class);
        DataSource.closeConnection(connection);
        return count;
    }

    public long countByUserId(String userId) throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "where id=?", new Object[]{userId}, News.class);
        DataSource.closeConnection(connection);
        return count;
    }

    public long countByDate(String date) throws SQLException {
        Connection connection = DataSource.getConnection();
        long count = DBUtil.count(connection, "where date=?", new Object[]{date}, News.class);
        DataSource.closeConnection(connection);
        return count;
    }
}
